EMT Practice Test

1. Question Content...

---

Question1: A DBA wants to create a table named SALES that has the following characteristics:
Effective data clustering and management of space utilization.
Rows in the table should be clustered based on when they are added to the table.
Which clause must be used with the CREATE TABLE statement to define this table?

Question2: The following query is executed frequently against the SALES table:
SELECT sales_person, region FROM sales WHERE region = 'Quebec'
Which statement will create an index for optimal query performance?

Question3: Which statement about IBM WebSphere Information Integrator Q Replication is correct?

Question4: Which index(es) are created when a multidimensional clustering (MDC) table is defined with only one dimension?

Question5: What are two services that the cluster caching facility (CF) provides in a DB2 pureScale environment?
(Choose two.)

Question6: Which type of storage is utilized in the implementation of an effective and flexible multi-temperature database?

Question7: What are two supported methods for providing a low latency, high speed interconnect between DB2 members and the cluster caching facility (CF) in a DB2 pureScale environment? (Choose two.)

Question8: Which constraints are used to tell the DB2 Optimizer to consider business rules that are not enforced by the database manager?

Question9: The following statement was used to create a table space named TBSP1 in a non-partitioned database:

Which statement(s) will successfully lower the high water mark in table space TBSP1 and return unused storage space to the operating system?

Question10: In what situation should you use a redirected restore operation?

Question11: What values can be assigned to the HADR_SYNCMODE configuration parameter?

Question12: Where is the HADR_TARGET_LIST configuration parameter set?

Question13: Which statement about data type mappings is true in a federated system?

Question14: What can be done to prevent applications from failing when DB2 cannot create a new log file?

Question15: An auditor has requested that all activities performed by those with SYSADM or DBADM authority be tracked. How can this be done with the db2audit tool?

Question16: When defining storage groups for multi-temperature data, storage paths are usually grouped according to their quality of service characteristics. What are three common quality of service characteristics used?

Question17: What advantage do block-based buffer pools offer compared to regular buffer pools?

Question18: Which statement about compression dictionaries is FALSE?

Question19: Which category must be enabled before db2audit will record the creation of new buffer pools?

Question20: A table named SALES was created as follows:

Assuming all table spaces required already exist, which statement(s) must be executed to add a new partition that already contains data for the first quarter of 2013 to the SALES table and leave the table accessible?

Question21: A database administrator created all of the necessary federated objects for an SQL Server data source in a federated system. Which steps must be performed if the data types of several columns in the SQL Server data source are changed?

Question22: Which statement about block indexes is correct?

Question23: Given the following output:

Which Explain procedure can be used to find the most expensive SQL statement in terms of CPU cost over a period of time?

Question24: On which two operating systems can DB2 pureScale run? (Choose two.)

Question25: Which would not significantly benefit from having the RUNSTATS command run on it?

Question26: Which Workload Manager (WLM) objects can the COLLECT ACTIVITY DATA clause be used with?

Question27: What type of file system is used to store data and logs in a DB2 pureScale environment?

Question28: Which statement about Range Clustered Tables (RCT) is correct?

Question29: Which table configuration CANNOT utilize multi-temperature storage?

Question30: In which situation would you NOT use a redirected restore?

Question31: For a multi-partitioned database, what is the default behavior of the BACKUP DATABASE command?

Question32: Which is a poor choice for a clustering dimension of a multidimensional clustering (MDC) table?

Question33: What must be done to convert an existing table into an Insert-Time-Clustered (ITC) table?

Question34: What is the recommended method for removing old backup images and log files for a DB2 10.1 database?

Question35: What two types of information can be obtained with the MON_GET_TABLESPACE monitor table function?
(Choose two.)

Question36: If a large amount of sequential prefetching is occurring, what can be done to improve efficiency?

Question37: A database named TEST was created and two buffer pools named BP_GENERAL and BP_AUDITING were created as follows:

The majority of the database tables were created in table space TS1, while one large table was created in table space TS2. A set of triggers that would populate the table in table space TS2 each time changes were made to the database were then defined and the database was put into production. At first, ad-hoc queries performing random I/O against the table in table space TS2 ran fine, but performance has gotten worse as the table has gotten larger. What should be done to improve query performance?

Question38: Which item is NOT eligible for adaptive compression?

Question39: You recently added more storage to a table space and a rebalance operation was started automatically.
How can you pause the rebalance operation?

Question40: Which table function can be used to determine whether all of a particular table space data objects have been moved from one storage group to another?

Question41: Which statement about Range-Clustered Tables (RCTs) is correct?

Question42: Which tool provides the simplest method for monitoring buffer pool hit ratios?

Question43: When will the existing data for a new partition be visible to applications querying a range-partitioned table?

Question44: What will occur when a database administrator adds a new automatic storage path to an existing storage group?

Question45: Which statement about Insert-Time-Clustered (ITC) is correct?

Question46: A DBA wants to create a table named SALES that has the following characteristics:
Data for the first half of the year will reside in table space TBSP1; the index for this data will reside in table space TBSP21.
Data for the second half of the year will reside in table space TBSP2; the index for this data will reside in table space TBSP22.
Which clause must be used with the CREATE TABLE statement to define this table?

Question47: If the following statements are executed:
CREATE STOGROUP sg_hot ON '/path1', '/path2', '/path3';
CREATE STOGROUP sg_med ON '/path4', '/path5', '/path6';
CREATE TABLESPACE tbsp1 USING STOGROUP sg_hot;
What is the recommended way to move table space TBSP1 from storage group SG_HOT to storage group SG_MED?

Question48: Consider the following statement:

Which indexes should be created for optimal query performance?

Question49: To configure instance-level auditing with db2audit, which authority is required?

Question50: In which scenario should a DBA consider creating a multidimensional clustering (MDC) table instead of a regular base table?

Question51: Which Workload Manager (WLM) objects can the COLLECT ACTIVITY DATA clause be used with?

Question52: What contributes to the efficient use of block-based buffer pools?

Question53: If the AUDIT_BUF_SZ configuration parameter is set to zero (0), what will happen?

Question54: A database administrator needs to create a federated database and configure access to join data from 3 Oracle instances, 2 SQL Server instances, and 1 DB2 database. Which objects are needed to establish the specified connections?

Question55: Which statement about Q Replication is correct?

Question56: You need to restore a table space that was backed up earlier, but before you begin a restore operation you want to determine whether any tables in the table space were populated with a LOAD ...
NONRECOVERABLE operation. What can be used to provide this information?

Question57: After starting a series of LOAD operations it appears that one LOAD is failing. What diagnostic tool would you use to determine which LOAD operation is being problematic?

Question58: Which statement about partitioned indexes is FALSE?

Question59: Which command(s) should be used to drop database partition 10 from a database named MYDB?

Question60: What is a benefit of multi-temperature data storage?

Question61: A query against the system catalog revealed that data for a table named SALES in a partitioned database is distributed as follows:

What can be done to improve the performance of a query that joins SALES and DATE_DIM tables (where SALES is a fact table and DATE_DIM is a dimension table) using the DATE_ID column?

Question62: What is the default user workload class that is created for each database table?

Question63: An auxiliary HADR standby database must use which HADR sync mode?

Question64: Which statement about dropped table recovery is correct?

Question65: In which situation would you NOT use a redirected restore?

Question66: With HADR, IBM Tivoli System Automation for Multiplatforms (SA MP) automated failover can be used with which database(s)?

Question67: A DBA wants to create a table named SALES that has the following characteristics:
Effective data clustering and management of space utilization
Rows in the table should be clustered based on when they are added to the table Which clause must be used with the CREATE TABLE statement to define this table?

Question68: A table space creation operation was unsuccessful because DB2 failed to allocate a container in a path.
Which diagnostic tool can be used to help resolve the problem?

Question69: Which Range-Clustered Tables (RCT) statement is correct?

Question70: Which statement about value compression is correct?

Question71: Which diagnostic tool can be used to collect metrics on individual SQL statement executions?

Question72: A database named TEST and a storage group named SG_1 were created with these statements:

an error was produced. Why did the error occur?

Question73: Which is NOT a valid method for prefetching data?

Question74: What is the preferred method for reclaiming unused storage in a DMS table space?

Question75: Which command will be executed successfully on every database partition if db2_all is used?

Question76: A DB2 9 database named MYDB that does not use automatic storage was migrated to DB2 10.1. When will a default storage group be defined for this database?

Question77: What is the FAILARCHPATH database configuration parameter used for?

Question78: Which operation can be performed by executing an ALTER STOGROUP statement?

Question79: The following query is executed frequently against the EMPLOYEE table:
SELECT empid, salary, bonus FROM employee ORDER BY bonus, salary;
Which statement will create an index for optimal query performance?

Question80: The following message is frequently seen in the DB2 Diagnostics Log file (db2diag.log):
MESSAGE:
ADM5502W
The escalation of "28581" locks on table "TEST.TABLE1" to lock intent "X" was successful.
Which two database configuration parameter settings should be checked? (Choose two.)

Question81: A DBA wants to create a set of tables in a storage group named SG_1, but no table spaces have been assigned to this group. What kind of table spaces must the DBA assign to the storage group before the desired tables can be created?

Question82: Which statement about the initiation of parallel I/O is correct?

Question83: The following query is executed frequently against the SALES table:
SELECT sales_id, sales_date, sales_person FROM sales;
If data in the SALES_ID column is unique,
Which statement will create an index for optimal query performance and ensure uniqueness of the SALES_ID column?

Question84: What statement regarding Asynchronous index cleanup (AIC) is correct?

Question85: Which monitoring tool can be used to obtain stripe set information for database managed storage table spaces?

Question86: A storage group named HOT_SG was created as follows:

What does the DATA TAG 1 clause do?

Question87: What is the default user workload class that is created for each database table?

Question88: What statement regarding Asynchronous index cleanup (AIC) is correct?

Question89: Which monitoring tool can be used to find out how many data pages have been read from the table space containers for regular, large, and temporary table spaces?

Question90: Which procedure is used to Explain an SQL statement using information obtained from the package cache or the package cache event monitor?

Question91: Which statement about DB2 Merge Backup is FALSE?

Question92: What event category is used with the db2audit command to capture SQL data, such as host variable and parameter marker values, that are associated with an SQL statement?

Question93: Which configuration parameter is used to designate a storage area for archived log files that are retrieved for recovery purposes?

Question94: Which configuration parameter setting(s) are needed to force authentication to happen at the data source in a federated database environment?

Question95: The following statement was used to create a table space named USER_TMP in a non-partitioned database:
CREATE USER TEMPORARY TABLESPACE user_tmp MANAGED BY SYSTEM USING
(mnt/fsystem4 mnt/fsystem5
Which operation can be performed on the USER_TMP table space with an ALTER TABLESPACE statement?

Question96: Which diagnostic tool can be used to show high-watermark consumptions of Fast Communication Manager (FCM) buffers and channels?

Question97: Which statement about SMS table spaces is correct?

Question98: Prior to migrating to DB2 10.1, a DBA executed the following command on a daily basis:
db2_all "<<-1< db2 BACKUP DATABASE sales TO /mnt/backup"
Which command can now be used to perform the same backup operation?

Question99: Which statement about compression dictionaries is FALSE?

Question100: Given the following CREATE TABLE statement:

Which statement is correct?

Question101: A storage group named MED_SG was created as follows:
CREATE STOGROUP med_sg ON db2fs1 db2fs2 '/db2fs3' Then, a table space named TS1 was created in the storage group and two tables (TAB_A and TAB_B) were created in the TS1 table space. Four months later, a DBA discovered that 90% of the space that had been allocated to the MED_SG storage group had been consumed. So, more space was added to the storage group by executing the following command:
ALTER STOGROUP med_sg ADD db2fs4
ALTER STOGROUP med_sg ADD db2fs4
Shortly afterwards, queries against tables in the TS1 table space began to run slower.
Which statement should be executed to correct the performance problem?

Question102: A table named MYTAB was created as follows:

How many rows will be inserted into the MYTAB table?

Question103: What must be done to convert a populated base table to a multidimensional clustering (MDC) table?

Question104: Given the following database design:

A Explain plan shows that a broadcast join is being used by a query that joins the DATE_DIM dimension table and the SALES fact table. Which action should be performed to improve query performance?

Question105: What must you do before an online table space backup operation can be performed?

Question106: To configure instance-level auditing with db2audit, which authority is required?

Question107: If the following commands complete successfully for a single partition database:
CREATE DATABASE mydb;
CREATE BUFFERPOOL mybp;
Which statement is true?

Question108: Which statement about DB2 Merge Backup is FALSE?